DROP TRIGGER lections_increase ON lesson;
DROP FUNCTION DeleteLectionIfPossible();

CREATE FUNCTION DeleteLectionIfPossible() RETURNS TRIGGER AS $$
DECLARE
    delete_lection_id integer := 0;
    lections_ids      integer[];
BEGIN
    SELECT array_agg(lesson_id) INTO lections_ids FROM lesson
        INNER JOIN lesson_type ON (lesson.type_id_lesson_type = lesson_type.type_id)
    WHERE subject_id_subject = NEW.subject_id_subject AND 
          lesson_type.type = 'лекция'
    LIMIT 1; 

    IF array_length(lections_ids, 1) > 0 AND 
       NEW.type_id_lesson_type = (SELECT type_id FROM lesson_type WHERE type = 'семинар') THEN
    BEGIN
        delete_lection_id := lections_ids[1];
        RAISE NOTICE 'Lection will be deleted';
        DELETE FROM lesson WHERE lesson_id = delete_lesson_id;
    END;
    END IF;

    RAISE NOTICE 'There is no lection to delete', delete_lection_id; 
    
END; 
$$language plpgsql;

CREATE TRIGGER lections_increase 
    AFTER INSERT ON lesson 
    FOR EACH ROW EXECUTE PROCEDURE DeleteLectionIfPossible();

CREATE EVENT TRIGGER ON CONNECT 

CREATE FUNCTION onlogin() RETURNS TRIGGER AS $$
BEGIN
    
END; 
$$language plpgsql;
